package com.yangyang.androidconnect.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class FileIndexDbHelper extends SQLiteOpenHelper {
    private static final String TAG = "FileIndexDbHelper";

    // 数据库信息
    private static final String DATABASE_NAME = "file_index.db";
    private static final int DATABASE_VERSION = 1;

    // 表名和列名
    public static final String TABLE_FILES = "file_index";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_PATH = "path";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_IS_FILE = "is_file";
    public static final String COLUMN_SIZE = "size";
    public static final String COLUMN_CREATION_TIME = "creation_time";
    public static final String COLUMN_LAST_MODIFIED = "last_modified";
    public static final String COLUMN_PARENT_PATH = "parent_path";
    public static final String COLUMN_EXTENSION = "extension";
    public static final String COLUMN_MIME_TYPE = "mime_type";

    // 创建表的SQL语句
    private static final String SQL_CREATE_TABLE =
            "CREATE TABLE " + TABLE_FILES + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY, " +
                    COLUMN_PATH + " TEXT NOT NULL UNIQUE, " +
                    COLUMN_NAME + " TEXT NOT NULL, " +
                    COLUMN_IS_FILE + " INTEGER NOT NULL, " +
                    COLUMN_SIZE + " INTEGER DEFAULT 0, " +
                    COLUMN_CREATION_TIME + " INTEGER DEFAULT 0, " +
                    COLUMN_LAST_MODIFIED + " INTEGER DEFAULT 0, " +
                    COLUMN_PARENT_PATH + " TEXT NOT NULL, " +
                    COLUMN_EXTENSION + " TEXT, " +
                    COLUMN_MIME_TYPE + " TEXT" +
                    ")";

    // 创建索引的SQL语句
    private static final String SQL_CREATE_INDEX_PARENT_PATH =
            "CREATE INDEX idx_parent_path ON " + TABLE_FILES + "(" + COLUMN_PARENT_PATH + ")";

    private static final String SQL_CREATE_INDEX_LAST_MODIFIED =
            "CREATE INDEX idx_last_modified ON " + TABLE_FILES + "(" + COLUMN_LAST_MODIFIED + ")";

    private static final String SQL_CREATE_INDEX_IS_FILE =
            "CREATE INDEX idx_is_file ON " + TABLE_FILES + "(" + COLUMN_IS_FILE + ")";

    private static final String SQL_CREATE_INDEX_PATH =
            "CREATE INDEX idx_path ON " + TABLE_FILES + "(" + COLUMN_PATH + ")";

    public FileIndexDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            // 创建表
            db.execSQL(SQL_CREATE_TABLE);

            // 创建索引
            db.execSQL(SQL_CREATE_INDEX_PARENT_PATH);
            db.execSQL(SQL_CREATE_INDEX_LAST_MODIFIED);
            db.execSQL(SQL_CREATE_INDEX_IS_FILE);
            db.execSQL(SQL_CREATE_INDEX_PATH);

            Log.d(TAG, "Database and indexes created successfully");
        } catch (Exception e) {
            Log.e(TAG, "Error creating database", e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            // 删除索引
            db.execSQL("DROP INDEX IF EXISTS idx_parent_path");
            db.execSQL("DROP INDEX IF EXISTS idx_last_modified");
            db.execSQL("DROP INDEX IF EXISTS idx_is_file");
            db.execSQL("DROP INDEX IF EXISTS idx_path");

            // 删除表
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_FILES);

            // 重新创建
            onCreate(db);
        } catch (Exception e) {
            Log.e(TAG, "Error upgrading database", e);
        }
    }
}